Relational databases store information in tables. A user retrieves information from the tables by entering input that is converted to queries by a database application. The database application submits the queries to a database server. In response to a query, the database server accesses the table specified in the query to determine which information within the tables satisfies the queries. The information that satisfies the queries is then retrieved by the database server and transmitted to the data application, and ultimately to the user.
For any given database server, the queries must conform to the rules of a particular query language. Most query languages provide users a variety of ways to specify information to be retrieved. For example, in the Structured Query Language (SQL), the query:
SELECT * FROM t1
requests the retrieval of the information contained in all rows of table "t1" Since a query directs a database server to perform a series of actions, a query is an example of a database "program unit."
When a database server executes the query, the database server must first allocate and configure execution memory for a "work area" used to store state and context information during the execution of the query. The structure of the work area is specific to a small set of similar queries. Various approaches have been developed for setting up the work area for a query.
In particular, a work area is configured by allocating a plurality of separately sized memory chunks. The number of memory chunks and size of each memory chunk are dependent on specific aspects of a query such as the number and nature of input variables and output variables for the query. The memory within each memory chunk is initialized to zero, and as another part of the configuration process, an array of pointers to these memory chunks is allocated and initialized. Allocating and configuring memory for the work area are both non-trivial, expensive operations.
According to a "per-use" approach, a specific memory area is dynamically allocated and configured as a work area for the user requesting the query for each execution of the query. When the execution of the query has completed, that memory is deallocated. If the user later submits another query, then another work area has to be dynamically allocated and configured before executing the query and deallocated afterwards. In other words, the cycle of allocating, configuring, and deallocating a work area is substantially repeated for each query. In particular, the process of configuring the work area is especially expensive, and it is desirable to avoid this expense.
The cost of the per-use allocation method may be illustrated with respect to a mail database system at a large company site. Typically, these mail database systems have a very large number of users, but each user only performs a small set of queries (i.e., fetch the new mail). If a mail database system has 10,000 users and each user fetches new mail twice a day, then the mail database system would have to allocate and configure 20,000 work areas throughout the day.
According to another approach, a work area is allocated and configured for a user in response to the first query the user submits. Instead of deallocating the work area when execution of the query completes, the database server maintains the work area for the continued use by that same user until the user logs out. In a subsequent query by the user during the same log in session, the previously allocated and configured work area for that session is reused for executing the query. This "per-session" approach saves the time it takes to allocate and configure the work area for a subsequent query by a user during the session.
In the example of the mail database system, if the 10,000 users log in at the beginning of the day and log out at the end of the day, and fetch their new mail twice, then the second query for each user would save the work area allocation and configuration costs. In this case, 10,000 work areas would be allocated and configured, half the number of the per-use approach. Accordingly, the per-session allocation approach works best when the number of separate, logged-in users is low. However, the per-session approach does not perform well when the number of users is large because maintaining an allocated and configured work area for each user may consume an unacceptably high amount of system resources for long periods. In this example, the mail database system would have to maintain 10,000 concurrent work areas in system memory by the end of the day.
A PL/SQL package is another example of a database program unit that is written in a high-level language and is called to direct a database server to perform a series of actions. When a package is called, the database system instantiates a package frame to maintain variables for the package. Like work areas for queries, the structure of a package frame is specific to a small set of similar packages and package frame instantiation has considerable allocation and configuration costs. For example, a package frame for a PL/SQL package contains an allocated and initialized dependency vector pointing to other PL/SQL packages that are needed to process this PL/SQL package. Furthermore, the memory requirements for global variables of a PL/SQL must be calculated, and the required memory must be allocated and initialized to zero. Accordingly, the configuration costs of instantiating a package frame are considerable.
Normally, the global variables instantiated in a PL/SQL package have values that persist as long as the user calling the package is logged in. In other words, such global variables have a session lifetime. Accordingly, PL/SQL package frames are instantiated on a per-session basis. Many packages, however, do not need to take advantage of the session lifetime feature for global variables. Nevertheless, these packages are conventionally instantiated according to a per-session approach with the same drawbacks as the per-session approach for allocating and configuring work areas for queries: system memory is excessively depleted.
Therefore, there is a need for a method of managing execution memory for database program units that neither incurs the expensive allocation and configuration costs of a per-use allocation scheme nor excessively depletes system memory in a per-session allocation scheme.